from common.connect_db import MyDB

my_db = MyDB()


class DataBaseOnGoods:

    @staticmethod
    def get_goods_id(shop_id):
        """
        根据店铺id查询商品id
        :param shop_id:
        :return:
        """
        goods_sql = "select id from life_online_goods_goods"" " \
                    "where shop_id='%s'and audit_status=3  and sale_status=1 and shelf_status=1 and activity_id=0" % shop_id
        goods_id = my_db.execute_select('ongoods', goods_sql)
        if goods_id[0]:
            return goods_id[0][0]
        else:
            print("数据出错了，请检查是否存在该店铺")

    # 获取商品sku_id
    def get_id(goods_id):
        sql = "select id from life_online_goods_goods_sku"" " \
              "where goods_id='%s'and inventory>=60" % goods_id
        sku_id = my_db.execute_select("ongoods", sql)
        if sku_id[0]:
            return sku_id[0][0]
        else:
            print("数据出错了，请检查是否存在该商品")

    def get_inventory(goods_id):
        sql = "select inventory from ongoods.life_online_goods_goods_sku"" " \
              "where goods_id='%s'" % goods_id
        inventory = my_db.execute_select("ongoods", sql)
        if inventory[0]:
            return inventory[0][0]
        else:
            print("数据出错了，请检查是否存在该商品")

    def updata_inventoryid(goods_id):
        sql = "UPDATE life_online_goods_goods_sku "" " \
              "set inventory=400  where goods_id='%s'" % goods_id
        result = my_db.execute_sql("ongoods", sql)
        if result:
            print("更新商品库存成功")
        else:
            print("程序出问题了")

    def get_shelf_status(id):
        """
        获取商品上架状态
        :return:
        """
        sql = "select shelf_status from life_online_goods_goods"" " \
              "where id = '%s'" % id
        shelf_status = my_db.execute_select("ongoods", sql)
        if shelf_status[0]:
            return shelf_status[0][0]
        else:
            print("数据出错了，请检查商品是否存在")

    def updata_shelf_status(id):
        sql = "UPDATE life_online_goods_goods_sku "" " \
              "set shelf_status=-1  where id='%s'" % id
        result = my_db.execute_sql("ongoods", sql)
        if result:
            print("更新商品上架状态成功")
        else:
            print("程序出问题了")

    @staticmethod
    def filter_life_online_goods_goods_by_name(goods_name):
        """
        通过商品名称找到商品信息
        :param goods_name: 商品名称（精确查找）
        :return: 商品信息
        """
        sql = "select * from life_online_goods_goods where name = '%s'" % goods_name
        result = my_db.execute_select('ongoods', sql)
        if len(result) == 1:
            return result[0]
        else:
            raise ValueError("找不到该商品")

    @staticmethod
    def filter_life_online_goods_goods_sku_by_goods_id(goods_id):
        """
        通过商品ID找到 对应的所有sku 信息
        :param goods_id: 商品id（精确查找）
        :return: sku详细信息
        """
        sql = "select * from life_online_goods_goods_sku where goods_id = '%s'" % goods_id
        result = my_db.execute_select('ongoods', sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("该商品没有SKU信息")


if __name__ == '__main__':
    data = DataBaseOnGoods()
